#!/usr/bin/env python
# -*- coding: utf8 -*-

import sqlite3
from config import Config
"""
Manage connection with database,
this is part of Pyspi,
DO NOT USE IT DIRECTLY
"""

class SQLMgr(object):
    """
    manage sqlite DB
    """
    def __init__(self):
        conf = Config()
        self.database = conf.database
    def connect(self):
        """
        connect with database
        """
        self.connection = sqlite3.connect(self.database)
        self.cursor = self.connection.cursor()
    def create(self):
        """
        create table in DB
        """
        try:
            self.cursor.execute('drop table pages')
            self.cursor.execute('drop table emails')
        except sqlite3.OperationalError:
             pass
        self.connection.commit()
        self.cursor.execute('create table pages (title text primary key,\
                    type text, size int)')
        self.cursor.execute('create table emails (address text primary key,\
                    page text references pages(title))') 
        self.connection.commit()
    def insert(self, row):
        """
        insert row in DB
        :parameters:
            row : tuple
                tuple containing record to put into DB in format:
                (title, type, size, [emails])
        """
        self.cursor.execute('select title from pages where title=(?)',\
                (row[0],))
        if not self.cursor.fetchall():
            self.cursor.execute('insert into pages values(?, ?, ?)',\
                    row[0:3])
            self.connection.commit()
            for i in row[3]:
                self.cursor.execute('select address from emails where \
                        address=(?)', (i,))
                if not self.cursor.fetchall():
                    self.cursor.execute('insert into emails values(?, ?)',\
                        (i, row[0]))
    def pull(self):
        """
        pull all rows and return list of records
        :parameters:
            rows : list
                all records in db
        """
        self.cursor.execute('select * from pages')
        rows=[]
        for i in self.cursor.fetchall():
            self.cursor.execute('select address from emails where page=(?)', (i[0],))
            row=[]
            row.extend(i)
            row.extend([ i for (i,) in self.cursor.fetchall()])
            rows.append(row)
        return rows
    def close(self):
        """
        close DB
        """
        self.cursor.close()
        self.connection.close()

if __name__ == "__main__":
    print "This is part of another module, don't use this directly"
